package org.mnj07.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;

public class Restaurants
{
  // private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";
  private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true";

  private static String tableName = "restaurants";

  // jdbc Connection
  private static Connection conn = null;

  private static Statement stmt = null;

  public static void main(String[] args)
  {
    createConnection();
    createTable();
    insertRestaurants(1, "PapaJohns", "Kendall Park");
    insertRestaurants(2, "Dominos", "Kendall Park");
    selectRestaurants();
    dropTable();
    closeConnection();
  }

  private static void dropTable()
  {
    try
    {
      System.out.println("Dropping table: " + tableName);
      stmt = conn.createStatement();
      String sql = "drop table " + tableName;
      stmt.execute(sql);
      stmt.close();
    }
    catch (SQLException sqlExcept)
    {
      sqlExcept.printStackTrace();
    }
  }

  private static void createTable()
  {
    try
    {
      System.out.println("Creating table: " + tableName);
      stmt = conn.createStatement();
      String sql = "create table " + tableName + " (id int primary key, restName varchar(20), cityName varchar(20))";
      stmt.execute(sql);
      stmt.close();
    }
    catch (SQLException sqlExcept)
    {
      sqlExcept.printStackTrace();
    }
  }

  private static void createConnection()
  {
    try
    {
      System.out.println("Creating connection ...");
      Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
      // Get a connection
      conn = DriverManager.getConnection(dbURL);
    }
    catch (Exception except)
    {
      except.printStackTrace();
    }
  }

  private static void insertRestaurants(int id, String restName, String cityName)
  {
    try
    {
      System.out.println("Inserting values into table: " + tableName);
      stmt = conn.createStatement();
      stmt.execute("insert into " + tableName + " values (" + id + ",'" + restName + "','" + cityName + "')");
      stmt.close();
    }
    catch (SQLException sqlExcept)
    {
      sqlExcept.printStackTrace();
    }
  }

  private static void selectRestaurants()
  {
    try
    {
      System.out.println("Retrieving values from table: " + tableName);
      stmt = conn.createStatement();
      ResultSet results = stmt.executeQuery("select * from " + tableName);
      ResultSetMetaData rsmd = results.getMetaData();
      int numberCols = rsmd.getColumnCount();
      for (int i = 1; i <= numberCols; i++)
      {
        // print Column Names
        System.out.print(rsmd.getColumnLabel(i) + "\t\t");
      }

      System.out.println("\n-------------------------------------------------");

      while (results.next())
      {
        int id = results.getInt(1);
        String restName = results.getString(2);
        String cityName = results.getString(3);
        System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
      }
      results.close();
      stmt.close();
    }
    catch (SQLException sqlExcept)
    {
      sqlExcept.printStackTrace();
    }
  }

  private static void closeConnection()
  {
    try
    {
      System.out.println("Closing connection.");
      if(stmt != null)
      {
        stmt.close();
      }
      if(conn != null)
      {
        //DriverManager.getConnection(dbURL + ";shutdown=true");
        conn.close();
      }
    }
    catch (SQLException sqlExcept)
    {
      sqlExcept.printStackTrace();
    }

  }
}
